package data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 
 * @author Niels Schmidt Hansen - niha12ai 
 * Initiates DbConnection class
 * 
 */
public class DbConnection {

	static final String DATABASE_URL = "jdbc:mysql://localhost:3306/mydb";
	ResultSet resultSet = null;
	Statement statement = null;
	Connection connection = null;

	private PreparedStatement selectAllUsers = null;
	private PreparedStatement selectUserAccounts = null;
	private PreparedStatement selectAllTransactions = null;
	private PreparedStatement insertNewUser = null;
	private PreparedStatement insertNewAccount = null;
	private PreparedStatement insertNewTransaction = null;
	private PreparedStatement updateUser = null;
	private PreparedStatement updateAccountSaldo = null;
	private PreparedStatement updateAvailableBalanceSaldo = null;

	public DbConnection() {
		/**
		 * This try/catch statements try to catch, compiling errors specific
		 * exception ex
		 */
		try {
			connection = DriverManager.getConnection(DATABASE_URL, "root", "");
			statement = connection.createStatement();
			selectAllUsers = connection.prepareStatement("SELECT * FROM Users");
			selectUserAccounts = connection
					.prepareStatement("SELECT * FROM Accounts");
			selectAllTransactions = connection
					.prepareStatement("SELECT * FROM Transaction");
			insertNewUser = connection.prepareStatement("INSERT INTO Users"
					+ "(Cpr_number, firstname, lastname, password)"
					+ "VALUES (?,?,?,?)");
			insertNewAccount = connection
					.prepareStatement("INSERT INTO Accounts(userid, availableBalance, totalBalance, kontoType) VALUES (?,?,?,?)");
			insertNewTransaction = connection
					.prepareStatement("INSERT INTO Transaction(accountId,TransactionText,amount,balance) VALUES (?,?,?,?)");
			updateUser = connection
					.prepareStatement("UPDATE Users SET firstname=?,lastname=?,password=? WHERE Cpr_number=?");
			updateAccountSaldo = connection
					.prepareCall("UPDATE Accounts SET totalBalance =? WHERE accountId =?");
			updateAvailableBalanceSaldo = connection
					.prepareCall("UPDATE Accounts SET availableBalance =? WHERE accountId =?");

		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * 
	 * @return ArrayList of all Users
	 */
	public List<User> getUsers() {
		List<User> uj = null;
		ResultSet resultSet = null;
		try {
			/**
			 * Exception handling ?
			 * 
			 * @returns uj
			 */
			resultSet = selectAllUsers.executeQuery();
			uj = new ArrayList<User>();
			while (resultSet.next()) {

				uj.add(new User(resultSet.getString("Cpr_number"), resultSet
						.getString("firstname"), resultSet
						.getString("lastname"), resultSet.getString("password")));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return uj;
	}

	/**
	 * Gets all Accounts in Arraylist
	 * 
	 * @return ak
	 */
	public List<Account> getAccounts() {
		List<Account> ak = null;
		ResultSet resultSet = null;
		try {

			resultSet = selectUserAccounts.executeQuery();
			ak = new ArrayList<Account>();
			while (resultSet.next()) {
				ak.add(new Account(resultSet.getInt("accountId"), resultSet
						.getString("userId"), resultSet
						.getDouble("availableBalance"), resultSet
						.getDouble("totalBalance"), resultSet
						.getString("kontoType")));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ak;
	}

	/**
	 * 
	 * Gets all transactions and the balances from the selected account
	 * 
	 * @return Transaction? / ak?
	 */
	public List<Transaction> getTransaction() {
		List<Transaction> ak = null;
		ResultSet resultSet = null;
		try {

			resultSet = selectAllTransactions.executeQuery();
			ak = new ArrayList<Transaction>();
			while (resultSet.next()) {
				ak.add(new Transaction(resultSet.getInt("accountId"), resultSet
						.getString("transactionText"), resultSet
						.getDouble("amount"), resultSet.getDouble("balance")));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ak;
	}

	/**
	 * addUser's constructor and all it's parameteres
	 * 
	 * @param cpr_num
	 * @param fname
	 * @param lname
	 * @param password
	 * @return result
	 */
	public int addUser(String cpr_num, String fname, String lname,
			String password) {
		int result = 0;
		try {
			insertNewUser.setString(1, cpr_num);
			insertNewUser.setString(2, fname);
			insertNewUser.setString(3, lname);
			insertNewUser.setString(4, password);

			result = insertNewUser.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return result;
	}

	/**
	 * addAccount's constructor and all it's parameters
	 * 
	 * @param userid
	 * @param availableBalance
	 * @param totalBalance
	 * @param kontoType
	 * @return result2
	 */
	public int addAccount(String userid, double availableBalance,
			double totalBalance, String kontoType) {
		int result2 = 0;
		try {
			insertNewAccount.setString(1, userid);
			insertNewAccount.setDouble(2, availableBalance);
			insertNewAccount.setDouble(3, totalBalance);
			insertNewAccount.setString(4, kontoType);

			result2 = insertNewAccount.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return result2;
	}

	/**
	 * addTransaction's constructor and all it's paramaters
	 * 
	 * @param accountId
	 * @param transactionText
	 * @param amount
	 * @param balance
	 * @return result2
	 */
	public int addTransaction(int accountId, String transactionText,
			double amount, double balance) {
		int result2 = 0;
		try {
			insertNewTransaction.setInt(1, accountId);
			insertNewTransaction.setString(2, transactionText);
			insertNewTransaction.setDouble(3, amount);
			insertNewTransaction.setDouble(4, balance);

			result2 = insertNewTransaction.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return result2;
	}

	/**
	 * getUpdateUsers constructor
	 * 
	 * @param Cpr_number
	 * @param firstname
	 * @param lastname
	 * @param password
	 * @return result
	 */
	public int getUpdateUsers(String Cpr_number, String firstname,
			String lastname, String password) {// return ArrayList

		int result = 0;
		try {

			updateUser.setString(1, Cpr_number);
			updateUser.setString(2, firstname);
			updateUser.setString(3, lastname);
			updateUser.setString(4, password);

			result = updateUser.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

	/**
	 * removeUser's constructor and it's parameter
	 * 
	 * @param name
	 *            Throws SQLException e?
	 */
	public void removeUser(String name) {
		try {
			statement.executeUpdate("delete from users where cpr_number ='"
					+ name + "'");
		} catch (SQLException e) {

			e.printStackTrace();
		}
	}

	/**
	 * removeAccounts constructor and it's parameter
	 * 
	 * @param name
	 *            Throws SQLexception e
	 */
	public void removeAccount(String name) {
		try {
			statement.executeUpdate("delete from accounts where userid ='"
					+ name + "'");
		} catch (SQLException e) {

			e.printStackTrace();
		}
	}

	/**
	 * updateAccountSaldo's constructor and it's parameters
	 * 
	 * @param totalBalance
	 * @param accountNumber
	 * @return result
	 */
	public int updateAccountSaldo(double totalBalance, int accountNumber) {
		{
			int result = 0;
			try {
				updateAccountSaldo.setDouble(1, totalBalance);
				updateAccountSaldo.setInt(2, accountNumber);
				result = updateAccountSaldo.executeUpdate();
			} catch (SQLException sqlException) {
				sqlException.printStackTrace();
			}
			return result;
		}

	}

	/**
	 * updateAvailableBalanceSaldo's constructor and it's parameters
	 * 
	 * @param availableBalance
	 * @param accountNumber
	 * @return result
	 */
	public int updateAvailableBalanceSaldo(double availableBalance,
			int accountNumber) {
		{
			int result = 0;
			try {
				updateAvailableBalanceSaldo.setDouble(1, availableBalance);
				updateAvailableBalanceSaldo.setInt(2, accountNumber);
				result = updateAvailableBalanceSaldo.executeUpdate();
			} catch (SQLException sqlException) {
				sqlException.printStackTrace();
			}
			return result;
		}
	}
}
